package com.tuanzi.loan.business.repository.receipt.statistics.impl;

import com.tuanzi.loan.business.entity.extend.ExtendInfo;
import com.tuanzi.loan.business.repository.receipt.statistics.custom.ReceiptExtendStatisticsRepositoryCustom;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.receipt.statistics.ReceiptExtendStatisticsSearchRequest;
import com.tuanzi.loan.vo.response.receipt.statistics.ReceiptExtendStatisticsSearchResponse;
import org.joda.time.DateTime;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import java.sql.Timestamp;
import java.text.NumberFormat;
import java.util.Date;
import java.util.List;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanReceiptExtendInfo.LOAN_RECEIPT_EXTEND_INFO;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanReceiptOrder.LOAN_RECEIPT_ORDER;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanReceiptProductInfo.LOAN_RECEIPT_PRODUCT_INFO;
import static com.tuanzi.loan.generated.jooq.loan.tables.StatLoanReceiptExtendInfo.STAT_LOAN_RECEIPT_EXTEND_INFO;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatNginxPromote.STAT_NGINX_PROMOTE;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatExtendRegisterUserCount.STAT_EXTEND_REGISTER_USER_COUNT;

/**
 * @Package: com.tuanzi.loan.business.repository.receipt.statistics.impl
 */
public class ReceiptExtendStatisticsRepositoryImpl extends JOOQBaseRepositoryPlus implements ReceiptExtendStatisticsRepositoryCustom {

    private static final String STAT_NGINX_PROMOTE_DATE_FORMAT = "yyyy-MM-dd";

    @Override
    protected TableImpl getTable() {
        return STAT_LOAN_RECEIPT_EXTEND_INFO;
    }

    public Page<ReceiptExtendStatisticsSearchResponse> list(ReceiptExtendStatisticsSearchRequest searchRequest, Pageable pageable) {
        fields(STAT_LOAN_RECEIPT_EXTEND_INFO.fields());
        ge(STAT_LOAN_RECEIPT_EXTEND_INFO.DATE, searchRequest.getStartDate());
        le(STAT_LOAN_RECEIPT_EXTEND_INFO.DATE, searchRequest.getEndDate());
        eq(STAT_LOAN_RECEIPT_EXTEND_INFO.ID, searchRequest.getChannelId());
        eq(STAT_LOAN_RECEIPT_EXTEND_INFO.PRODUCT_ID, searchRequest.getReceiptProductId());
        orderBy(STAT_LOAN_RECEIPT_EXTEND_INFO.DATE, Sort.Direction.DESC);
        return search(pageable, new ExtendStatSeaRespMapper());
    }


    @Override
    public List<ExtendInfo> findAllExtendInfos() {
      return  dslContext.select(LOAN_RECEIPT_EXTEND_INFO.fields()).select(LOAN_RECEIPT_PRODUCT_INFO.NAME).from(LOAN_RECEIPT_EXTEND_INFO)
                .leftJoin(LOAN_RECEIPT_PRODUCT_INFO).on(LOAN_RECEIPT_EXTEND_INFO.PRODUCT_ID.eq(LOAN_RECEIPT_PRODUCT_INFO.ID))
                .orderBy(LOAN_RECEIPT_EXTEND_INFO.PRODUCT_ID.asc()).fetch().into(ExtendInfo.class);
    }

    @Override
    public Integer findOrderCountByProdIdAndChanelIdAndDate(Long productId, String channelId, Date startTime, Date endTime) {
        return dslContext.fetchCount(dslContext.select(LOAN_RECEIPT_ORDER.ID).from(LOAN_RECEIPT_ORDER).where(LOAN_RECEIPT_ORDER.PRODUCT_ID.eq(productId))
                .and(LOAN_RECEIPT_ORDER.CHANNEL_ID.eq(channelId)).and(LOAN_RECEIPT_ORDER.COMMIT_TIME.between(new Timestamp(startTime.getTime()), new Timestamp(endTime.getTime()))));
    }


    private final class ExtendStatSeaRespMapper implements RecordMapper<Record, ReceiptExtendStatisticsSearchResponse> {

        @Override
        public ReceiptExtendStatisticsSearchResponse map(Record record) {
            ReceiptExtendStatisticsSearchResponse response = new ReceiptExtendStatisticsSearchResponse();
            response.setDate(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.DATE));
            response.setApplyCount(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.APPLY_COUNT));
            response.setChannelLink(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.CHANNEL_LINK));
            response.setChannelName(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.CHANNEL_NAME));
            response.setLinkUV(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.LINK_UV));
            response.setProductId(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.PRODUCT_ID));
            response.setProductName(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.PRODUCT_NAME));
            response.setRegisterCount(record.get(STAT_LOAN_RECEIPT_EXTEND_INFO.REGISTER_COUNT));
            NumberFormat numberFormat= NumberFormat.getInstance();
            numberFormat.setMaximumFractionDigits(2);
            //访问->申请转化率
            if (response.getLinkUV() == 0) {
                response.setRate("0%");
            } else {
                response.setRate(numberFormat.format((float) response.getApplyCount() / (float) response.getLinkUV() * 100) + "%");
            }
            return response;
        }
    }

    @Override
    public Integer getRegisterCount(String channelId, Date startDate,Date endDate) {
        return dslContext.select(STAT_EXTEND_REGISTER_USER_COUNT.REGISTER_COUNT).from(STAT_EXTEND_REGISTER_USER_COUNT).where(STAT_EXTEND_REGISTER_USER_COUNT.CHANNEL.eq(channelId))
                .and(STAT_EXTEND_REGISTER_USER_COUNT.CREATE_DATE.between(new java.sql.Date(startDate.getTime()), new java.sql.Date(endDate.getTime()))).fetchOne(STAT_EXTEND_REGISTER_USER_COUNT.REGISTER_COUNT);
    }

    @Override
    public Integer getNginxUV(String url, Date date) {
        String dateStr=new DateTime(date).toString(STAT_NGINX_PROMOTE_DATE_FORMAT);
        return dslContext.select(STAT_NGINX_PROMOTE.UV).from(STAT_NGINX_PROMOTE).where(STAT_NGINX_PROMOTE.URL.eq(url))
                .and(STAT_NGINX_PROMOTE.TIME.eq(dateStr)).fetchOne(STAT_NGINX_PROMOTE.UV);
    }

}
